/**
This file is part of dbdjasperreport.

Copyright (C) 2008 Kue Vang aka Kou
Licensed under the Eclipse Public License, Version 1.0; 
you may not use this file except in compliance with the License. 
You may obtain a copy of the License at http://www.eclipse.org/legal/epl-v10.html 
Unless required by applicable law or agreed to in writing, software 
distributed under the License is distributed on an "AS IS" BASIS, 
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 
See the License for the specific language governing permissions and limitations 
under the License.
**/
  
import java.text.DateFormat
import java.text.SimpleDateFormat
import groovy.sql.Sql
import java.util.Calendar
import java.util.GregorianCalendar
import org.apache.tools.ant.util.FileUtils


class WhereClauseService  implements java.io.Serializable{
    javax.sql.DataSource dataSource
	DateFormat dfMySql = new SimpleDateFormat("yyyy-MM-dd")
	StringBuffer whereClause 
	DataTypeService dataTypeService
	
	def String generateWhereClause(HashMap paramMap, Integer systemId) {
		def sql = getSql()
		whereClause = new StringBuffer()
		
		def rptSystem = ReportSystem.get(systemId)
		def dbType = rptSystem.databaseType
		
		//println "dbType = "+dbType
		paramMap.each{key,param->
			//println "param in generateWhereClause "+param
			String dataType = param.dataType.toString()
				if (dataType == DataTypeService.dateDataType){
					if(rptSystem.databaseType.toUpperCase()=="ORACLE")
						applyDateFilterOracle(param)
				    else{
						applyDateFilterMySql(param)
					//println "after applyDateFilterMySql "+whereClause
					}
				} 
				else if (dataType== DataTypeService.numDataType) {
					applyNumberFilter(param)
				} 
				else if (dataType == DataTypeService.strDataType) {
					applyStringFilter(param)
				} 
				else if (dataType == DataTypeService.codeDataType | 
				         dataType == DataTypeService.entityDataType) {
					applyCodeEntityFilter(param)
				} 
				else if (dataType == DataTypeService.booleanDataType) {
					applyBooleanFilter(param)
				} 
				else if(dataType == DataTypeService.yesNoDataType){
					applyYesNoFilter(param)			
				}			
		}
		
		String tmpWhereClause =""
		if(whereClause)
			tmpWhereClause = "WHERE "+whereClause.toString()
		println "whereClause = " + tmpWhereClause
        return tmpWhereClause

    }
	
	private HashMap assignParameter(nonFilterableParam){
	    HashMap parameters = [:]
		nonFilterableParam.each{param->			
		    if(param.dataType == Integer.valueOf(dataTypeService.rtfDataType)){
				String rtfText
				//rtfText = FileUtils.readFully(new FileReader(new File(param.parameterValue,param.parameterValue2)))
				File file = new File(param.parameterValue)
				if(file.canRead())
					rtfText = FileUtils.readFully(new FileReader(file))
					
				parameters.put(param.parameterName,rtfText)
			}
			else
				parameters.put(param.parameterName,param.parameterValue)
 		}
		return parameters
	}
	
	private applyDateFilterMySql(param){
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.allDates) {// all dates
			// include All dates so do nothing			
		} 
		else if (id == OperatorService.lastWeek) {// Last week	
			Calendar cal = getLastWeekDate()		
			
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, 6)
			
			String dateLastWeek = dateToMySqlDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d')" +
						" BETWEEN " +dateLastWeek)				
			} else {
				whereClause.append(" DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d')" +
						" BETWEEN " +dateLastWeek)
			}			
		} 
		else if (id == OperatorService.lastMonth) {// Last Month
			Calendar cal = getLastMonthDate()
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH)-1)
			String lastMonthDate = dateToMySqlDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName +",'%Y-%m-%d') " +
						" BETWEEN " + lastMonthDate)
			} else {
				whereClause.append(" DATE_FORMAT("+param.parameterName +",'%Y-%m-%d') " +
						" BETWEEN " + lastMonthDate)
			}
		} else if (id == OperatorService.dateBetween && param.dateBetween1 && param.dateBetween2) {
			Calendar cal = new GregorianCalendar()
			Calendar cal2 = new GregorianCalendar()
			cal.setTime(param.dateBetween1)
			cal2.setTime(param.dateBetween2)
			
			String dateBetween = dateToMySqlDateString(cal,cal2)
			//println "dateBetween = " + dateBetween
			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + ",'%Y-%m-%d') "
						+ " BETWEEN " + dateBetween)
			} else {
				whereClause.append(" DATE_FORMAT("+param.parameterName + ",'%Y-%m-%d') " 
						+ " BETWEEN "+dateBetween)
			}
		} else if (id == OperatorService.datePastDays && param.datePastDay) {// dates within the past days
			java.util.Date date = new java.util.Date()
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(date)
			calendar1.add(Calendar.DAY_OF_MONTH, -param.datePastDay)
			String days = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') >= "	+ days)

			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') >= " + days)
			}
		} else if (id == OperatorService.datePastMonths && param.datePastMonth) {// dates within the past months
			java.util.Date date = new java.util.Date()
			Calendar cal = new GregorianCalendar()
			cal.setTime(date)
			cal.add(Calendar.MONTH, -param.datePastMonth)
			cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))
			
			String months = dateToMySqlDateString(cal)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') >= "	+ months)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') >= " + months)
			}
		} else if (id == OperatorService.dateLessEqual && param.dateLessEqual) {// Less than or Equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateLessEqual)
			String dateAsOf = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName + 
						",'%Y-%m-%d') <= "	+ dateAsOf)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName + 
						",'%Y-%m-%d') <= " + dateAsOf)
			}
		} else if (id == OperatorService.dateGreaterEqual && param.dateGreaterEqual) {// >= greater then or equal to
			//println"in greaterEqual mysql "
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateGreaterEqual)
			String dateGrEq = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName  + 
						",'%Y-%m-%d') >= "	+ dateGrEq)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName  + 
						",'%Y-%m-%d') >= " + dateGrEq)
			}
		} else if (id == OperatorService.dateEqual && param.dateEqual) {// = equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateEqual)
			String dateEq = dateToMySqlDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and DATE_FORMAT(" + param.parameterName  + ",'%Y-%m-%d') = "
						+ dateEq)
			} else {
				whereClause.append(" DATE_FORMAT("+ param.parameterName  + ",'%Y-%m-%d') = " + dateEq)
			}
		}
	}
	
	private applyDateFilterOracle(param){
		//println "in applyDateFilterOracle"
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.allDates) {// all dates
			// include All dates so do nothing			
		} 
		else if (id == OperatorService.lastWeek) {// Last week	
			Calendar cal = getLastWeekDate()		
			
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, 6)
			
			String dateLastWeek = dateToOracleDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName +")" +
						" BETWEEN " +dateLastWeek)				
			} else {
				whereClause.append(" TRUNC(" + param.parameterName +")" +
						" BETWEEN " +dateLastWeek)
			}			
		} 
		else if (id == OperatorService.lastMonth) {// Last Month
			Calendar cal = getLastMonthDate()
			Calendar cal2 = new GregorianCalendar()
			cal2.setTime(cal.getTime())
			cal2.add(Calendar.DAY_OF_MONTH, cal.getActualMaximum(Calendar.DAY_OF_MONTH)-1)
			String lastMonthDate = dateToOracleDateString(cal,cal2)
			
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName +") " +
						" BETWEEN " + lastMonthDate)
			} else {
				whereClause.append(" TRUNC("+param.parameterName +") " +
						" BETWEEN " + lastMonthDate)
			}
		} else if (id == OperatorService.dateBetween && param.dateBetween1 && param.dateBetween2) {//date between
			Calendar cal = new GregorianCalendar()
			Calendar cal2 = new GregorianCalendar()
			cal.setTime(param.dateBetween1)
			cal2.setTime(param.dateBetween2)
			
			String dateBetween = dateToOracleDateString(cal,cal2)
			//println "dateBetween = " + dateBetween
			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + ") "
						+ " BETWEEN " + dateBetween)
			} else {
				whereClause.append(" TRUNC("+param.parameterName + ") " 
						+ " BETWEEN "+dateBetween)
			}
		} else if (id == OperatorService.datePastDays && param.datePastDay) {// dates within the past days
			java.util.Date date = new java.util.Date()
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(date)
			calendar1.add(Calendar.DAY_OF_MONTH, -param.datePastDay)
			String days = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") >= "	+ days)

			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") >= " + days)
			}
		} else if (id == OperatorService.datePastMonths && param.datePastMonth) {
			java.util.Date date = new java.util.Date()
			Calendar cal = new GregorianCalendar()
			cal.setTime(date)
			cal.add(Calendar.MONTH, -param.datePastMonth)
			cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))
			
			String months = dateToOracleDateString(cal)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") >= "	+ months)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") >= " + months)
			}
		} else if (id == OperatorService.dateLessEqual && param.dateLessEqual) {// Less than or Equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateLessEqual)
			String dateAsOf = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName + 
						") <= "	+ dateAsOf)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName + 
						") <= " + dateAsOf)
			}
		} else if (id == OperatorService.dateGreaterEqual && param.dateGreaterEqual) {// >= greater then or equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateGreaterEqual)
			String dateGrEq = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName  + 
						") >= "	+ dateGrEq)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName  + 
						") >= " + dateGrEq)
			}
		} else if (id == OperatorService.dateEqual && param.dateEqual) {// = equal to
			Calendar calendar1 = new GregorianCalendar()
			calendar1.setTime(param.dateEqual)
			String dateEq = dateToOracleDateString(calendar1)

			if (whereClause.size() > 0) {
				whereClause.append(" and TRUNC(" + param.parameterName  + ") = "
						+ dateEq)
			} else {
				whereClause.append(" TRUNC("+ param.parameterName  + ") = " + dateEq)
			}
		}
	}
	
	private applyNumberFilter(param){
		String id = param.idOperatorSelected.toString()
		if (id == OperatorService.numEqual && param.numEqual) { // equals
			if (whereClause.size() > 0)
				whereClause.append(" and " + param.parameterName + " = " + param.numEqual)
			else 
				whereClause.append(param.parameterName + " = "	+ param.numEqual)
		} 
		else if (id == OperatorService.numNotEqual && param.numNotEqual) { // not equal to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " <> " + param.numNotEqual)
			else 
				whereClause.append(param.parameterName + " <> "+ param.numNotEqual)
		} 
		else if (id == OperatorService.numBetween && param.numBetween1 && param.numBetween2) { // between
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName
						+ " BETWEEN " + param.numBetween1 + " and " + param.numBetween2)
			else 
				whereClause.append(param.parameterName + " BETWEEN "
							+ param.numBetween1 + " and " + param.numBetween2)
		} 
		else if (id == OperatorService.numLessEqual && param.numLessEqual) {// Less then or = to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " <= " + param.numLessEqual)
			else
				whereClause.append(param.parameterName + " <= "+ param.numLessEqual)
		} 
		else if (id == OperatorService.numGreaterEqual && param.numGreaterEqual) {// Greater then or = to
			if (whereClause.length() > 0) 
				whereClause.append(" and " + param.parameterName+ " >= " + param.numGreaterEqual)
			else 
				whereClause.append(param.parameterName + " >= "+ param.numGreaterEqual)
		}
		
	}
	
	private applyStringFilter(param){
		String id = param.idOperatorSelected.toString()
		if (param.strInput){
			if (id == OperatorService.strStartsWith) { // Starts With
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER("
							+ param.parameterName.trim() + ") LIKE "
							+ "'" + param.strInput.toUpperCase().trim() + "%' ")
				} else {
					whereClause.append(" UPPER("
							+ param.parameterName + ") LIKE "
							+ "'" + param.strInput.toUpperCase() + "%' ")
				}
			} 
			else if (id == OperatorService.strContains) { // Contains
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER(" + param.parameterName + ")" + 
							" LIKE '%" + param.strInput.toUpperCase() + "%' ")
				} else {
					whereClause.append(" UPPER(" + param.parameterName + ")" +
							" LIKE '%" + param.strInput.toUpperCase() + "%' ")
				}
			} 
			else if (id == OperatorService.strMatchesExactly) { // Matches exactly not case sensitive
				if (whereClause.size() > 0) {
					whereClause.append(" and UPPER("
							+ param.parameterName + ") = " + "'" + param.strInput.toUpperCase()
							+ "' ")
				} else {
					whereClause.append(" UPPER("+ param.parameterName + ") = "
							+ "'" + param.strInput.toUpperCase() + "' ")
				}
			}
		}
	}
	
	private applyCodeEntityFilter(param){
		String id = param.idOperatorSelected.toString()
		StringBuffer strCode = new StringBuffer()
		String tmpClause =""
		
		if (param.codeSelectedList) {
		//Review this code to see if you can use the each key word for looping through the list.
		// if the PK is a number for CODE and Entity should always have a number for it's Primary Key
			if (id == OperatorService.codeIdIsNumber | 
			    id == OperatorService.none) { 
				for(int i = param.codeSelectedList.size()-1;i>=0;i--){
				    Code code = param.codeSelectedList[i]
					if(strCode)
						strCode.append(", "+code.id)
					else
						strCode.append("("+code.id)
				}
			} 
			else { // string
				for(int i = param.codeSelectedList.size()-1;i>=0;i--){
				    Code code = param.codeSelectedList[i]
					if(strCode)
						strCode.append(", '"+code.id +"'")
					else
						strCode.append("('"+code.id + "'")
				}
			}

			if (strCode.size() > 0) {
				// "(" is added above before the first value and ")" is added
				// after the last value for the 'IN' operator for oracle
				strCode.append(")")
				tmpClause = param.parameterName + " IN " + strCode
				if (whereClause.size() > 0)
					whereClause.append(" and " + tmpClause)
				else
					whereClause.append(tmpClause)				
			}
		}
	}
	
	private applyYesNoFilter(param){
		String id = param.idOperatorSelected.toString()
		if(id ==OperatorService.yesNoY){//Yes 
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  "Upper(" + param.parameterName + 
						") LIKE 'Y%' ")
			} else {
				whereClause.append("Upper(" + param.parameterName + ") LIKE 'Y%' ")
			}
		}
		else if(id == OperatorService.yesNoN){//No
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  "Upper(" + param.parameterName + 
						") LIKE 'N%' ")
			} else {
				whereClause.append("Upper(" + param.parameterName + ") LIKE 'N%' ")
			}
		}
	}
	
	private applyBooleanFilter(param){
		String id = param.idOperatorSelected.toString()
		if(id ==OperatorService.booleanTrue){//True
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  param.parameterName + " = 1")
			} else {
				whereClause.append(param.parameterName + " = 1 ")
			}
		}
		else if(id == OperatorService.booleanFalse){// False
			if (whereClause.size() > 0) {
				whereClause.append(" and " +  param.parameterName +	" = 0 ")
			} else {
				whereClause.append(param.parameterName + " = 0 ")
			}
		}
	}
	
	public Calendar getLastWeekDate(){
		Calendar cal = new GregorianCalendar()
		cal.setTime(new Date())
		if(cal.get(Calendar.DAY_OF_WEEK)==1)
			cal.add(Calendar.DAY_OF_MONTH, -7)
		else if(cal.get(Calendar.DAY_OF_WEEK)==2)
			cal.add(Calendar.DAY_OF_MONTH, -8)
		else if(cal.get(Calendar.DAY_OF_WEEK)==3)
			cal.add(Calendar.DAY_OF_MONTH, -9)
		else if(cal.get(Calendar.DAY_OF_WEEK)==4)
			cal.add(Calendar.DAY_OF_MONTH, -10)
		else if(cal.get(Calendar.DAY_OF_WEEK)==5)
			cal.add(Calendar.DAY_OF_MONTH, -11)
		else if(cal.get(Calendar.DAY_OF_WEEK)==6)
			cal.add(Calendar.DAY_OF_MONTH, -12)
		else if(cal.get(Calendar.DAY_OF_WEEK)==7)
			cal.add(Calendar.DAY_OF_MONTH, -13)
		return cal
	}
	
	public Calendar getLastMonthDate(){
		Calendar cal = new GregorianCalendar()
		cal.setTime(new Date())
		cal.add(Calendar.MONTH, -1)//take us back 1 month
		cal.add(Calendar.DAY_OF_MONTH, - (cal.get(Calendar.DAY_OF_MONTH)-1))//take us back to the first of the month
		return cal
	}
	
	//return the date as an MySQL date string
	public String dateToMySqlDateString(Calendar cal){
		//MySQL date string example: '2008-01-22'
		String date="'"+dfMySql.format(cal.getTime())+"'"
		return date
	}
	
	//return the date as an MySQL date string
	public String dateToMySqlDateString(Calendar cal, Calendar cal2){
		//MySQL date string example: '2008-01-22'
		String date="'"+dfMySql.format(cal.getTime()) + "' and '" + dfMySql.format(cal2.getTime())+"'"
		return date
	}
	
	//return the date as an Oralce date string
	public String dateToOracleDateString(Calendar cal){
		//Oracle date string example: '1-jan-2008'
		String date=""
		String year1 = Integer.toString(cal.get(Calendar.YEAR))
		// calendar1.MONTH start from 0-11 so we have to add 1.
		String month1 = Integer.toString((cal.get(Calendar.MONTH) + 1))
		String day1 = Integer.toString(cal.get(Calendar.DAY_OF_MONTH))
		date="'"+day1+"-"+getMonthShort(month1)+"-"+ year1+ "' "
		return date
	}
	
	//return the two date as an Oracle date string with 
	//the word 'and' between the dates
	public String dateToOracleDateString(Calendar cal,Calendar cal2){
		String date=""
		
		String year1 = Integer.toString(cal.get(Calendar.YEAR))
		// calendar1.MONTH start from 0-11 so we have to add 1.
		String month1 = Integer.toString((cal.get(Calendar.MONTH) + 1))
		String day1 = Integer.toString(cal.get(Calendar.DAY_OF_MONTH))
		String year2 = Integer.toString(cal2.get(Calendar.YEAR))
		String month2 = Integer.toString((cal2.get(Calendar.MONTH) + 1))
		String day2 = Integer.toString(cal2.get(Calendar.DAY_OF_MONTH))
		
		date="'"+day1+"-"+getMonthShort(month1)+"-"+ year1+ 
		     "' and " + 
		     "'"+day2+"-"+getMonthShort(month2)+"-"+ year2+"' "
		return date
	}
	
	private getMonthShort(String month){
		if(month=='1')
			return 'jan'
		else if(month =='2')
			return 'feb'
		else if(month =='3')
			return 'mar'
		else if(month =='4')
			return 'apr'
		else if(month =='5')
			return 'may'
		else if(month =='6')
			return 'jun'
		else if(month =='7')
			return 'jul'
		else if(month =='8')
			return 'aug'
		else if(month =='9')
			return 'sep'
		else if(month =='10')
			return 'oct'
		else if(month =='11')
			return 'nov'
		else if(month =='12')
			return 'dec'
	}

    private getSql(){
        return new Sql(dataSource)
    }

 

}